#!/user/bin/python
# -*- coding:utf-8 -*-
#filename:sqlmap.py

'''
SQLMAP  sql语句集合文件
Created on 2011-8-25

@author: zc
'''

""" 全部tfeet第一次载入 """
SQL_ALL1ST = """select u.uname, u.uid, u.description, u.profile_image_url,
                                        t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t
                                    where u.uid = t.uid
                                    order by tfid desc
                                    limit %d
                                """

""" 全部tfeet第N次载入  N>=2"""
SQL_ALLMANY = """select u.uname, u.uid, u.description, u.profile_image_url,
                                        t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t
                                    where  u.uid = t.uid and t.tfid < %d   
                                    order by tfid desc
                                    limit %d
                                """
                                
""" 全部tfeet第N次载入  N>=2,从旧到新"""
SQL_ALLMANY_OLD2NEW = """select u.uname, u.uid, u.description, u.profile_image_url,
                                        t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t
                                    where  u.uid = t.uid and t.tfid > %d   
                                    order by tfid asc
                                    limit %d
                                """
                                

""" 我的tfeet第1次载入 """                                
SQL_USER1ST = """select u.uname, u.uid, u.description, u.profile_image_url,
                                            t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                        from user u, tfeet t
                                        where u.uid = t.uid and t.uid = %d 
                                        order by tfid desc
                                        limit %d
                                    """

""" 我的tfeet第N次载入  N>=2"""
SQL_USERMANY = """select u.uname, u.uid, u.description, u.profile_image_url,
                                            t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                        from user u, tfeet t
                                        where u.uid = t.uid and t.uid = %d  and t.tfid < %d   
                                        order by tfid desc
                                        limit %d
                                    """        

""" 我的tfeet第N次载入  N>=2,从旧到新"""
SQL_USERMANY_OLD2NEW = """select u.uname, u.uid, u.description, u.profile_image_url,
                                            t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                        from user u, tfeet t
                                        where u.uid = t.uid and t.uid = %d  and t.tfid > %d   
                                        order by tfid asc
                                        limit %d
                                    """

""" 我的收藏的tfeet第1次载入 """
SQL_USER1ST_FAV = """select u.uname, u.uid, u.description, u.profile_image_url,
                                    t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t, tf_favorite tf
                                    where u.uid = t.uid and t.tfid = tf.tfid and  tf.uid = %d
                                    order by t.tfid desc
                                    limit %d
                                    """     
                                    
""" 我收藏的tfeet第N次载入  N>=2"""                                    
SQL_USERMANY_FAV = """select u.uname, u.uid, u.description, u.profile_image_url,
                                    t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t, tf_favorite tf
                                    where u.uid = t.uid and t.tfid = tf.tfid and  tf.uid = %d and t.tfid < %d 
                                    order by t.tfid desc
                                    limit %d
                                    """

""" 我收藏的tfeet第N次载入  N>=2,从旧到新"""                                    
SQL_USERMANY_FAV_OLD2NEW = """select u.uname, u.uid, u.description, u.profile_image_url,
                                    t.tfid, t.text,t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t, tf_favorite tf
                                    where u.uid = t.uid and t.tfid = tf.tfid and  tf.uid = %d and t.tfid > %d 
                                    order by t.tfid asc
                                    limit %d
                                    """
                                    
""" 根据tfid获取评论数量"""
SQL_GETCOMMENTSNUM = "select count(*) as cnt from comments where tfid = %s"

SQL_GETCOMMENTS1ST = """select u.uname, u.uid, c.cid, c.comment, c.date
                        from comments c, user u
                        where c.uid = u.uid and c.tfid = %d
                        order by cid desc
                        limit %d
                    """

SQL_GETCOMMENTSMANY = """select u.uname, u.uid, c.cid, c.comment, c.date
                        from comments c, user u
                        where c.uid = u.uid and c.tfid = %d and c.cid < %d
                        order by cid desc
                        limit %d
                """                                

SQL_MYFEETS = "select count(*) cnt from tfeet where uid = %d" 

SQL_MYCMTS = "select count(*) cnt from comments where uid = %d"

SQL_THCMTS = """select sum(c.cnt) cnt
                        from tfeet t, (select tfid, count(*) cnt from comments group by tfid) c
                        where t.tfid = c.tfid and t.uid = %d
                        """

SQL_MYFAVS = "select count(*) cnt from tf_favorite where uid = %d"                                                     



""" 搜索tfeet第一次载入 """
SQL_SEARCH1ST = """select u.uname, u.uid, u.description, u.profile_image_url,
                                        t.tfid, t.text,  t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t
                                    where u.uid = t.uid and (t.place like '%%%s%%' or t.text like '%%%s%%')
                                    order by t.tfid desc
                                    limit %d
                                """

""" 搜索tfeet第N次载入  N>=2"""
SQL_SEARCHMANY = """select u.uname, u.uid, u.description, u.profile_image_url,
                                        t.tfid, t.text,  t.pic_normal, t.pic_small, t.pic_original, t.date, t.location, t.longitude, t.latigude, t.like, t.dislike
                                    from user u, tfeet t
                                    where u.uid = t.uid   and (t.place like '%%%s%%' or t.text like '%%%s%%')  and t.tfid < %d 
                                    order by t.tfid desc
                                    limit %d
                                """

""" 根据tfid获取tfeet信息"""
SQL_GETTFEET = """select u.uname, t.text, t.pic_normal, t.pic_small, t.pic_original,  t.longitude, t.latigude,t.weibo_id
                                    from user u, tfeet t
                                    where u.uid = t.uid  and t.tfid = %d 
                                """


# 增加用户
SQL_ADD_USER = """INSERT INTO user(uid,uname,province,city,location,description,profile_image_url,avatar_large,url,domain,gender,regdate,lastlogin,lastip)
VALUES(%s, '%s', %s, %s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, '%s')"""

#更新用户信息
SQL_UPDATE_USER = """UPDATE user
SET  uid = %s, uname = '%s', province = %s,
     city = %s, location =  '%s',description =  '%s', profile_image_url =  '%s',
     avatar_large =  '%s', url =  '%s', domain =  '%s', gender =  '%s',
     lastlogin =  %s,lastip =  '%s'
WHERE uid = %s"""

# 查询用户信息
SQL_GET_USER = """SELECT id,uid,uname,province,city,location,description,profile_image_url,avatar_large,url,domain,gender,regdate,lastlogin,lastip
FROM user
WHERE uid = %d"""

#判断用户是否存在
SQL_GET_CNTUSER = """ SELECT count(*) as cnt FROM user WHERE uid = %s """

#判断weibo2tfeet是否存在
SQL_GET_WEIBOID = """ SELECT count(*) as cnt FROM tfeet WHERE weibo_id = %s """

#new tfeet
SQL_ADD_TFEET = """INSERT INTO tfeet(uid,location,date,longitude,latigude,text,pic_normal,pic_small,pic_original,source,weibo_id )
VALUES(%s,'%s',%s,'%s','%s','%s','%s','%s','%s','%s','%s')"""

# 获取新增tfeet的tfid
SQL_GET_ADD_TFID = """SELECT tfid
FROM tfeet
WHERE uid = %s and
    location='%s' and 
    date = %s and
    longitude = %s and
    latigude = %s and
    text = '%s' and
    pic_normal = '%s' and
    pic_small = '%s' and
    pic_original = '%s' and
    source = '%s' and
    weibo_id = '%s'
"""
#update tfeet
SQL_UPDATE_TFEET = """ UPDATE  tfeet
SET location = '%s',
        date = %s,
        longitude = %s,
        latigude = %s,
        text = '%s',
        pic_normal = '%s',
        pic_small = '%s',
        pic_original = '%s'
WHERE tfid = %s"""

#get since_id from tfeet, source = 'weibo'
SQL_GET_SINCE_ID_WEIBO = """SELECT IFNULL(MAX(weibo_id),0) AS since_id FROM tfeet
WHERE source='weibo' AND weibo_id IS NOT NULL"""
#根据uid tfid 日期获取评论id
SQL_GETCID = "select cid from comments where uid = %s and tfid = %s and date=%s limit 1"
#删除评论
SQL_DEL_COMMENT = "DELETE FROM comments WHERE cid = %s "

#删除一条我想去
SQL_DEL_IWANT = "DELETE FROM tf_favorite WHERE uid = %s and tfid = %s " 

#删除一条tfeet
SQL_DEL_TFEET = "DELETE FROM tfeet WHERE tfid = %d"
#判断当前用户是否收藏该tfeet
SQL_IS_IWANT = "SELECT count(1) as isfav FROM tf_favorite WHERE uid = %d and tfid = %d"
